package model.admin;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.sun.xml.internal.bind.v2.runtime.unmarshaller.XsiNilLoader.Array;

import myutil.Connect;

import vo.mixed.CourseListVo;
import vo.mixed.ProfessorListVo;

public class GetListsMo {
	public ArrayList<String> getCourseList(int batch_id, int professor_id,
			int student_id) {
		// This method populates lists for adding course to a particular batch
		// or professor or student
		// author: Ashwini

		String query = "";
		ArrayList<String> course_list = new ArrayList<String>();
		if (batch_id != 0)
			query = "select c.course_name," + " c.course_code,"
					+ " c.course_id , " + " dd.department_name, "
					+ " dd.department_id" + " from degree_department d, "
					+ " course_details c, " + " department_details dd "
					+ "where d.department_id	=	dd.department_id "
					+ "and c.department_id	=	d.department_id "
					+ "and d.batch_id		!=	" + batch_id + "" 
					+ "and c.status			=	1;";

		// if(professor_id!=0)
		query = "select c.course_name from course_details c where c.status=1";
		if (professor_id != 0)
			query = "select c.course_name " +
					"  from course_details c" +
					" where c.status=1 and c.course_id " +
					"not in (select course_id " +
							  "from professor_department " +
							 "where (professor_id_1		="+professor_id+" " +
							 	"or  professor_id_2		="+professor_id+" " +
							 	"or  professor_id_3		="+professor_id+" " +
							 	"or  professor_id_4		="+professor_id+"))";
		System.out.println("\n Query is " + query);
		
		Connect c = new Connect();
		try {
			ResultSet rs = c.getResult(query);
			while (rs.next()) {
				String single_object;
				single_object = rs.getString("c.course_name");
				course_list.add(single_object);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println(e.getMessage());
		}

		return course_list;
	}

	public ArrayList<String> department_list(int batch_id) {
		String query = "";

		query = "select d.department_name" + "from degree_department dd, "
				+ "     department_details d "
				+ "where d.department_id		=		dd.department_id"
				+ "  and dd.batch_id			=		" + batch_id 
				+ "  and d.status				=		1;";
		ArrayList<String> department_list = new ArrayList<String>();
		Connect c = new Connect();

		try {
			ResultSet r = c.getResult(query);
			while (r.next()) {
				String single_object;
				single_object = r.getString("d.department_name");
				department_list.add(single_object);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return department_list;
	}

	public ArrayList<Integer> GetYearList() {
		ArrayList<Integer> yearList = new ArrayList<Integer>();

		for (int i = 2000; i < 2020; i++) {
			yearList.add(i);
		}
		return yearList;
	}

	public ArrayList<String> getDegreeList() {
		ArrayList<String> degree_list = new ArrayList<String>();
		String query = "";
		query = "select d.degree_name from degree_details d where d.status=1";
		Connect c = new Connect();
		try {
			ResultSet r = c.getResult(query);
			while (r.next()) {
				String single_object;
				single_object = r.getString("d.degree_name");
				degree_list.add(single_object);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return degree_list;
	}

	public ArrayList<String> getInchangeList() {
		ArrayList<String> incharge_list = new ArrayList<String>();
		String query = "select staff_id,staff_name from college_staff where status=1;";
		Connect c = new Connect();
		try {
			ResultSet r = c.getResult(query);
			while (r.next()) {
				String single_object;
				single_object = r.getString("staff_name");
				System.out.println(single_object);
				incharge_list.add(single_object);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}

		return incharge_list;
	}

	public ArrayList<String> getDepartmentList() {
		ArrayList<String> dept_list = new ArrayList<String>();
		String query = "select department_name from department_details where status=1;";
		Connect c = new Connect();
		try {
			ResultSet r = c.getResult(query);
			while (r.next()) {
				String single_object;
				single_object = r.getString("department_name");
				System.out.println(single_object);
				dept_list.add(single_object);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return dept_list;
	}

	public ArrayList<String> getStudentList() {
		ArrayList<String> student_list = new ArrayList<String>();
		String query = "select * from student_details where student_id=(select student_id from student_batch where batch_id =0) and status=1;";
		Connect c = new Connect();
		try {
			ResultSet r = c.getResult(query);
			while (r.next()) {
				String single_object;
				single_object = r.getString("student_name");
				System.out.println(single_object);
				student_list.add(single_object);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return student_list;
	}
	public ArrayList<String> getAllCourses()
		{
		ArrayList<String> courses= new ArrayList<String>();
		String query="select * from course_details where status=1";
		Connect c = new Connect();
		try {
			ResultSet r = c.getResult(query);
			while (r.next()) {
				String single_object;
				single_object = r.getString("course_name");
				System.out.println(single_object);
				courses.add(single_object);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return courses;
		}
}
